package model;

import java.sql.*;
import java.util.Vector;

public class DBControler {

		private Connection con = null;;
		private Statement stmt = null;
		private PreparedStatement add = null;
		private PreparedStatement all = null;
		private PreparedStatement search = null;

		public DBControler() {
			try {
				Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
				con = DriverManager.getConnection("jdbc:derby:db-data;create=false");
				stmt = con.createStatement();
				ResultSet result = stmt.executeQuery("SELECT TABLENAME FROM SYS.SYSTABLES");
				result.next();
				if (!result.getString(1).equals("BOOKING")) {
					stmt.executeUpdate("create table BOOKING (	description varchar(50), resource varchar(50), sign varchar(10), bTime time, bDate date)");
				}
//				searchPnr = con.prepareStatement("SELECT * FROM BOOKING WHERE description=?");
//				remove = con.prepareStatement("DELETE FROM ANMAL WHERE PNR=?");
//				add = con.prepareStatement("INSERT INTO ANMAL VALUES (?, ?, ?)");
				all = con.prepareStatement("SELECT * FROM BOOKING",
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				add = con.prepareStatement("INSERT INTO BOOKING (description, resource, sign, bTime, bDate) VALUES (?,?,?,?,?)",
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				search = con.prepareStatement("SELECT * FROM BOOKING WHERE bDate=? AND bTime=? AND resource=?",
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				//con.createStatement().executeUpdate("DELETE FROM BOOKING");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();

			} /*
				 * catch (ClassNotFoundException e) { // TODO Auto-generated catch
				 * block System.out.println(e.getMessage()); e.printStackTrace(); }
				 */catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}

//		public boolean resetTable() {
//			try {
//				Statement s = con.createStatement();
//				s.executeUpdate("DELETE FROM ANMAL");
//
//			} catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//				return false;
//			}
//			return true;
//
//			// TODO Auto-generated method stub
//
//		}
//
//		public boolean addRecord(Record r) {
//			try {
//				add.setString(1, r.getPnr());
//				add.setString(2, r.getName());
//				add.setString(3, r.getClub());
//				add.executeUpdate();
//			} catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//				return false;
//			}
//			return true;
//
//			// TODO Auto-generated method stub
//
//		}

		public Vector<Booking> allRecords() {
			Vector<Booking> bookings = new Vector<Booking>();
			try {
				ResultSet rs = all.executeQuery();
				for (int i = 0; rs.next(); i++) {
					Booking book = readBooking(rs);
					bookings.add(book);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			// TODO Auto-generated method stub
			return bookings;
		}

		private Booking readBooking(ResultSet rs) throws SQLException {
			Booking book = new Booking();
			book.setDescription((String)rs.getObject(Booking.description_colname));
			book.setResource((String)rs.getObject(Booking.resource_colname));
			book.setSign((String)rs.getObject(Booking.sign_colname));
			book.setDate((java.sql.Date)rs.getObject(Booking.date_colname));
			book.setTime((java.sql.Time)rs.getObject(Booking.time_colname));
			return book;
		}

		public void close() {
			try {

				con.close();

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			// TODO Auto-generated method stub

		}

//		public Record firstRecord() {
//			Record tmp = null;
//			if (m == null) {
//				allRecords();
//				try {
//					m.beforeFirst();
//					m.next();
//					tmp = new Record(m.getString(1), m.getString(2), m.getString(3));
//				} catch (SQLException e) {
//					// TODO Auto-generated catch block
//					e.printStackTrace();
//				}
//
//			}
//			// TODO Auto-generated method stub
//			return tmp;
//		}
//
//		public boolean removeRecord(Record tmp) {
//			try {
//				remove.setString(1, tmp.getPnr());
//				remove.executeUpdate();
//
//			} catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//				return false;
//			}
//
//			// TODO Auto-generated method stub
//			return true;
//		}
//
//		public Record searchRecord(String s) {
//			Record r = null;
//			try {
//				searchPnr.setString(1, s);
//				ResultSet rs = searchPnr.executeQuery();
//				while (rs.next()) {
//					r = new Record();
//					r.setPnr(rs.getString("PNR"));
//					r.setName(rs.getString("NAME"));
//					r.setClub(rs.getString("CLUB"));
//				}
//			} catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
//			// TODO Auto-generated method stub
//			return r;
//		}
		public Connection getCon() {
			return con;
		}

//		public static void main(String[] args) {
//			// TODO Auto-generated method stub
//			SimpleDB d = new SimpleDB();
//			d.addRecord(new Record("121212-1212","Pelle", "FBK"));
//			//d.resetTable();
//			Record[] rr = d.allRecords();
//
//			for (int i = 0; i < rr.length; i++) {
//				System.out.println(rr[i].getPnr() + " " + rr[i].getName() + " "
//						+ rr[i].getClub());
//			}
//		}
//
//		
//
//	}

	public Booking retrieveSelection(Booking booking) throws SQLException{
		search.setDate(1, booking.getDate());
		search.setTime(2, booking.getTime());
		search.setString(3, booking.getResource());
		ResultSet rs = search.executeQuery();
		if(rs.next()){
			return readBooking(rs);
		} else {
			return null;
		}
	}

	public void saveBooking(Booking booking) throws SQLException {
		add.setString(1, booking.getDescription());
		add.setString(2, booking.getResource());
		add.setString(3, booking.getSign());
		add.setTime(4, booking.getTime());
		add.setDate(5, booking.getDate());
		add.executeUpdate();
	}
}
